import pandas as pd
import jieba
from collections import Counter 
import json
import pymysql

class DataClear():
    def __init__(self):
        db = pymysql.connect(  
            host='localhost',  # 数据库服务器地址，本地数据库使用'localhost'  
            user='root',  # 数据库用户名  
            password='123456',  # 数据库密码  
            database='jobdata',  # 数据库名称  
            charset= 'utf8mb4'
        )
        self.df = pd.read_sql('select * from jobtitle',db)
        self.df.drop('id',axis=1,inplace=True)
        self.df.drop_duplicates(inplace=True)
        self.df1 = pd.read_sql('select * from jobinfo',db)
    #查询高薪岗位并筛选
    def sa(self,x):
        small = x.split('-')[0]
        small = int(small)
        return 'h' if small >= 8 else 'l'

    #词频统计函数
    def count_words(self,text):  
        # 去除中文标点符号  
        for i in ['。','（','）','？',"！",'，','(',')','/','【','】']:
            text = text.replace(i,"")
        # 将文本分割为单词列表  
        words = text.split("-")  
        # 统计词频  
        word_counts = Counter(words)  
        return word_counts  

    def wordClound(self):
        self.df['salary_p'] = self.df['price'].map(self.sa)
        #显示高薪岗位
        self.df_h =  self.df[self.df['salary_p'] == 'h']
        df_h = self.df_h
        #使用python的jieba库对高薪岗位数据进行分词，然后存入列表中
        ans = []
        #遍历高薪岗位名称
        for i in df_h['name']:
            #存入列表
            ans.append("-".join(jieba.cut(i)))
        #下面进行词频统计，因为词云图的文字是需要权重的，这里使用词频
        strs = "-".join(ans)
        words = self.count_words(strs)
        word = sorted(words,key=lambda x:words[x],reverse=True)
        #去除空白项
        result = [[i,words[i]] for i in word if i != '' and i != ' ' and i != '+']
        #jieba尚不完全，手动拼接一下
        result[-2][-1]+=7
        result[-2][0] = '后端'
        result[-6][-1]+=9
        result[-6][0] = '大数据'
        result = sorted(result,key=lambda x:x[-1],reverse=True)

        return result[:(len(result)//2)]
    
    def wordClound_(self):
        self.df['salary_p'] = self.df['price'].map(self.sa)
        #显示高薪岗位
        df_h =  self.df[self.df['salary_p'] == 'h']
        #再来一个高薪的公司名词词云图
        #上面很多都封装好了，这里不再解释
        ltd = []
        for i in df_h['ltd']:
            #这里不再使用jiebe分割，因为公司名称分割毫无意义
            ltd.append(i)
        #python可以支持同名变量，但是要记得当前变量的值
        words = self.count_words("-".join(ltd))
        word = sorted(words,key=lambda x:words[x],reverse=True)
        #拿出前20个
        result_l = [[i,words[i]] for i in word]

        return result_l[:(len(result_l)//4)]
    
    #根据实际我们选择最低薪资进行分析
    def maps(self,x):
        small = x.split('-')[0]
        small = int(small)
        if small <= 3:
            return '1-3'
        elif 3<small<=5:
            return '3-5'
        elif 5<small<=7:
            return '5-7'
        elif 7<small<=9:
            return '7-9'
        else:
            return '>9'
        
    def pie(self):
        self.df['count_s'] = self.df['price'].map(self.maps)
        #统计各个薪资岗位数
        c_v = self.df['count_s'].value_counts()
        dic = json.loads(c_v.to_json())
        
        res = [{
            'name':i+'k',
            'value':dic[i]
        } for i in dic]

        return res

    #根据实际我们选择最低薪资进行分析
    def map_p(self,x):
        small = x.split('-')[0]
        small = int(small)
        return small if small<20 else 3
    
    def line(self):
        self.df['salary'] = self.df['price'].map(self.map_p)
        #按照经验分组
        ans = self.df.groupby('experience')['salary'].mean()
        dic = json.loads(ans.to_json())
        res = {
            'name':[i for i in dic],
            'value':[dic[i] for i in dic]
        }
        return res
    
    def line_max_min(self):
        self.df['salary'] = self.df['price'].map(self.map_p)
        #最大薪资
        ans1 = self.df.groupby('experience')['salary'].max()
        #最小薪资
        ans2 = self.df.groupby('experience')['salary'].min()
        dic = json.loads(ans1.to_json())
        dic2 = json.loads(ans2.to_json())
        res = {
            'name':[i for i in dic],
            'max':[dic[i] for i in dic],
            'min':[dic2[i] for i in dic2]
        }
        return res
    def job_count(self):
        dic = json.loads(self.df.groupby('experience').count()['name'].to_json())
        res = [{
            'name':i,
            'value':dic[i]
        } for i in dic]
        return res

    def high_salary(self):
        self.df['salary_p'] = self.df['price'].map(self.sa)
        #显示高薪岗位
        df_h =  self.df[self.df['salary_p'] == 'h']
        dic = df_h.groupby('experience').count()['ltd']
        dic = json.loads(dic.to_json())
        res = [{
            'name':i,
            'value':dic[i]
        } for i in dic]
        return res